/*
 * 
 * Version 1.00
 * 
 * Created on 2007-6-4
 * Encoding GB2312
 */
package net.comm.check.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

import net.comm.common.GlbFinal;
import net.comm.common.ReturnMessage;
import net.comm.message.service.impl.MessageServiceImpl;
import net.comm.message.web.form.MessageForm;
import net.comm.message.web.form.MsgcontForm;
import net.comm.param.service.impl.SequenceMgr;
import net.comm.common.common;


import net.comm.check.bean.BaseChecklog;
import net.comm.check.db.DbChecklogService;
import net.comm.check.db.ChecklogServiceSqlMysql;
import net.comm.check.web.form.ChecklogForm;
import net.comm.check.web.form.JifenForm;
import net.comm.page.PageCtr;

import net.comm.shequ.service.impl.WuyeServiceImpl;
import net.comm.shequ.service.impl.YeweiServiceImpl;
import net.comm.shequ.web.form.YeweiForm;
import net.comm.tags.JstlFun;
import net.comm.user.service.impl.UserSysinfoServiceImpl;
import net.comm.user.web.form.UserSysinfoForm;
import net.comm.util.DatetimeUtil;
import net.comm.util.DbcpPool;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.log4j.Logger;
import org.sakaiproject.db.impl.BasicSqlService;


/**
 *
 * @author hcgong(hcgong@sina.com)
 * @version 1.0
 * @date 2007-6-4
 */
public class ChecklogServiceImpl {

	static Logger logger = Logger.getLogger(ChecklogServiceImpl.class.getName());

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List list(ChecklogForm theForm,  ReturnMessage msg) 
	{
		List list = null;

		Connection cn = null;

		try
		{
			cn = DbcpPool.getConnection();
			
			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			/*
			 * 获得查询SQL语句
			 */
			String sql = servSql.getCountSql1();

			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(bSqlServ.dbCount(cn, sql, null));
			page.caculate();

			//取得FORM中传入的查询参数
			Object[] fields2 = new Object[2];
			
			fields2[1] = page.getPos();
			fields2[2] = page.getPageSize();
			
			sql = servSql.getRecbyPageSql();

			
			list = bSqlServ.dbRead(cn, sql, fields2, new DbChecklogService());
			
		    DbcpPool.returnConnection(cn);
		    
		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
			
		} finally {
			DbcpPool.returnConnection(cn);
		}		
		
		return list;
	}

	/**
	 * 
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#list(net.swift.module.other.ExaQueryBean, net.swift.common.ReturnMessage)
	 */
	public List adlist(ChecklogForm theForm,  ReturnMessage msg) 
	{
		List list = new Vector();

		Connection cn = null;
		Statement stmt=null;
		ResultSet rs=null;
		try
		{
			cn = DbcpPool.getConnection();
			
			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			
			StringBuffer sb = new StringBuffer(" where 1=1");
			if(theForm.getQryCheckerID() > 0){
				
				sb.append(" and CheckerID = ");
				sb.append(theForm.getQryCheckerID());
				sb.append(" ");
			}
			if(theForm.getQryUserID() > 0){
				
				sb.append(" and UserID = ");
				sb.append(theForm.getQryUserID());
				sb.append(" ");
			}
			sb.append(" Order by Mtime desc ");
			
			//查询记录数
			stmt = cn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			String sql = servSql.getCountSql1();

			logger.debug(sql+sb.toString());
			rs=stmt.executeQuery(sql+sb.toString());
			int nRows = 0;
			if (rs.next())
	           nRows = rs.getInt("COUNT");
			else
			{
	            rs.close();
	            stmt.close();
	            DbcpPool.returnConnection(cn);
			    return list;
			}
			/**
			 * 根据SQL执行结果计算分页相关信息
			 */
			PageCtr page = theForm.getPage();
			page.setTotalNum(nRows);
			page.caculate();

			sb.append(" limit ");
			sb.append(page.getPos()-1);
			sb.append(", ");
			sb.append(page.getPageSize());
			
			 //取记录
			 sql = servSql.getRecbyPageSql();
			 logger.debug(sql+sb.toString());
			 DbChecklogService dbservice = new DbChecklogService();
			 
			 rs=stmt.executeQuery(sql+sb.toString());
			 while (rs.next())
			 {
				try
				{
					Object obj = dbservice.readSqlResultRecord(rs);
					if (obj != null) list.add(obj);
				}
				catch (Throwable t)
				{
					logger.error(t.getMessage());
				}		
			 }
			 rs.close();
			 stmt.close();
		     DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			/**
			 * 记录相关日志
			 */
			logger.error(ex.getMessage());
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				DbcpPool.returnConnection(cn);
			} catch (Exception ex1) {
				ex1.printStackTrace();
			}			
	    }finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
			} catch (Exception ex) {
				ex.printStackTrace();
			}			
			DbcpPool.returnConnection(cn);
		}	
		return list;
	}
	

	/**
	 * @see net.IUserbasicService.module.other.IUserService
	 * 		#get(net.swift.db.tab_paramnostro, net.swift.common.ReturnMessage)
	 */
	public void get(ChecklogForm theForm, ReturnMessage msg) 
	{
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			DbcpPool.beginTran(cn);
			
			List list = null;

			//取得FORM中传入的查询参数
			Object[] fields2 = new Object[1];
			
			fields2[0] = theForm.getSeqID();
			
			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			String sql = servSql.getbykeySql();			
					
			list = bSqlServ.dbRead(cn, sql, fields2, new DbChecklogService());
			
			if(list.size() ==0){
				common.setAppFailMsg(msg , " 取记录失败，请检查！");
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;				
			}
			
			BaseChecklog base = (BaseChecklog)list.get(0);
				
			
			BeanUtils.copyProperties(theForm, base); 
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);
			return;
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about info" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
//	/**
//	 * 发送验证短信
//	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
//	 */
//	public void YanzhMsg(ChecklogForm theForm, ReturnMessage msg) 
//	{
//		
//		Connection cn = null;
//		try
//		{
//			cn = DbcpPool.getConnection();
//			
//			DbcpPool.beginTran(cn);
//			
//			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
//			BasicSqlService  bSqlServ = new BasicSqlService();
//			
//			//检查是否已经做过验证，做过则返回
//
//			Object[] fields2 = new Object[3];
//			
//			fields2[0] = theForm.getCheckerType();
//			fields2[1] = theForm.getCheckerID();
//			fields2[2] = theForm.getUserID();
//			
//			String sql = servSql.getIsExistSql();			
//					
//			List list = bSqlServ.dbRead(cn, sql, fields2, new DbChecklogService());
//			if(list.size() > 0){
//				common.setAppFailMsg(msg , " 已经做过验证！");
//			}	
//			
//			
//			
//			//发送邮件
//			String title = "验证申请";
//			String username = JstlFun.GetUserNick(theForm.getUserID());
//			String content = username + "申请会员验证";
//			String mtype = GlbFinal.MTYPE_YANZHENG;
//			int receiver = theForm.getCheckerID();
//			
//			if(theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_YEWEI)){
//				
//				//取业委联系人 作为邮件接收者
//				YeweiServiceImpl yeweiservice = new YeweiServiceImpl();
//				YeweiForm yeweiform = new YeweiForm();
//				
//				yeweiform.setYeweiID(theForm.)
//				
//				title = "申请业委会验证";
//				content = username + " 申请业委会验证";
//				mtype = GlbFinal.MTYPE_YWYANZH;
//			}else if(theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_WUYE)){
//			
//				//取物业负责人作为邮件接收者
//				
//				title = "申请物业处验证";
//				content = username + " 申请物业处验证";
//				mtype = GlbFinal.MTYPE_WYYANZH;
//			}
//
//			MessageServiceImpl msgservice = new MessageServiceImpl();
//			MessageForm msgForm = new MessageForm();
//			MsgcontForm msgcontForm = new MsgcontForm();
//			
//			msgForm.setReceiver(theForm.getCheckerID());
//			msgForm.setSender(theForm.getUserID());
//			msgForm.setMtype(mtype);
//			
//			msgForm.setTitle(title);
//			msgcontForm.setMsg(content);
//			
//			msgservice.add(cn, msgForm, msgcontForm, msg);
//			if(msg.getErrCode() != 0){
//				return;
//			}
//			
//			DbcpPool.commitTran(cn);
//			DbcpPool.closeTran(cn);
//			DbcpPool.returnConnection(cn);
//		} catch(Exception ex) 
//		{
//			common.setAppFailMsg(msg , ex.getMessage());
//			logger.error("System error , param get about info" + ex);
//			try 
//			{
//				DbcpPool.rollBackTran(cn);
//				DbcpPool.closeTran(cn);
//				DbcpPool.returnConnection(cn);
//			} catch(Exception ex1) 
//			{
//				ex1.printStackTrace();
//				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
//			}			
//		} finally {
//			DbcpPool.returnConnection(cn);
//		}
//		
//		return ;			
//	}
//	
	/**
	 * 添加到表中
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(ChecklogForm theForm, ReturnMessage msg) 
	{
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);
			
			add(cn, theForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			common.setAppFailMsg(msg , ex.getMessage());
			logger.error("System error , param get about info" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}
	/**
	 * 添加到表中
	 * @see net.IUserbasicService.module.other.IUserService#add(net.swift.common.ReturnMessage)
	 */
	public void add(Connection cn, ChecklogForm theForm, ReturnMessage msg) 
	{
		
		try
		{
			
			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			
			//检查是否已经做过验证，做过则返回
			List list = null;

			Object[] fields2 = new Object[3];
			
			fields2[0] = theForm.getCheckerType();
			fields2[1] = theForm.getCheckerID();
			fields2[2] = theForm.getUserID();
			
			String sql = servSql.getIsExistSql();			
					
			list = bSqlServ.dbRead(cn, sql, fields2, new DbChecklogService());
			if(list.size() > 0){
				common.setAppFailMsg(msg , " 已经做过验证！");
				return;				
			}	
			
			//ID
			theForm.setSeqID(SequenceMgr.generateSeq(GlbFinal.SEQ_INITDATE,GlbFinal.SEQ_CHECKLOGID));	

			UserSysinfoServiceImpl syservice = new UserSysinfoServiceImpl();
			UserSysinfoForm sysform = new UserSysinfoForm();

			//取验证者等级
			if(theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_USERS)){
				
				sysform = syservice.list(theForm.getCheckerID());
				theForm.setCheckerLevel(sysform.getLevel());
			}
			
			//取被验证者等级
			sysform = syservice.list(theForm.getUserID());
			theForm.setUserLevel(sysform.getLevel());
			
			//取得FORM中传入的查询参数
			Object[] fields = new Object[7];
			
			fields[0] = theForm.getSeqID();
			fields[1] = theForm.getCheckerType();
			fields[2] = theForm.getCheckerID();
			fields[3] = theForm.getCheckerLevel();
			fields[4] = theForm.getUserID();
			fields[5] = theForm.getUserLevel();
			fields[6] = DatetimeUtil.getDatetime();
			
			sql = servSql.getInsertSql();
						
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 添加失败！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return ;
			}
			//计算积分
			
			int fnID = 0;
			
			//业委会或物业管理处可对会员身份给予验证，个人会员可获得100积分
			if(theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_WUYE) ||
					theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_YEWEI)){
				
				fnID = 6;
			}else{
				//如果普通会员之间的相互验证，双方各获得10积分
				if(theForm.getCheckerLevel() == 1 && theForm.getUserLevel() == 1){
					fnID = 3;
				}else
				//认证会员之间及认证会员与普通会员之间的互相验证，双方各获得100积分				
				if(theForm.getCheckerLevel() == 2 && theForm.getUserLevel() == 2 ||
						theForm.getCheckerLevel() == 2 && theForm.getUserLevel() == 1 ||
						theForm.getCheckerLevel() == 1 && theForm.getUserLevel() == 2){
					fnID = 4;
				}else
				//荣誉会员之间及荣誉会员与其他会员之间的互相验证，双方各获得300积分
				if(theForm.getCheckerLevel() == 3 && theForm.getUserLevel() == 3 ||
							theForm.getCheckerLevel() == 3  ||
							theForm.getUserLevel() == 3){
					fnID = 5;
				}
			}
			JifenServiceImpl jifenservice = new JifenServiceImpl();
			JifenForm jifenForm = new JifenForm();
			
			jifenForm.setFnID(fnID);
			
			jifenservice.get(cn, jifenForm, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			//更新被验证会员积分
			sysform.clear();
			sysform.setUserID(theForm.getUserID());
			sysform.setIntegral(jifenForm.getJifen());
			
			syservice.update(cn, sysform, msg);
			if(msg.getErrCode() != 0){
				return;
			}
			//如果是会验证 则增加验证方的积分
			if(theForm.getCheckerType().equals(GlbFinal.CHECKTYPE_USERS)){
				sysform.clear();
				sysform.setUserID(theForm.getCheckerID());
				sysform.setIntegral(jifenForm.getJifen());
				
				syservice.update(cn, sysform, msg);
				if(msg.getErrCode() != 0){
					return;
				}
			}
			
		} catch(Exception ex) 
		{
			common.setAppFailMsg(msg , ex.getMessage());
			logger.error("System error , param get about info" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
		}
		
		return ;
	}
	/**
	 * 删除一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void delete(ChecklogForm theForm, ReturnMessage msg) 
	{
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			
			DbcpPool.beginTran(cn);

			//取得FORM中传入的查询参数
			Object[] fields2 = new Object[1];
			
			fields2[0] = theForm.getSeqID();
			
			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();
			String sql = servSql.getDeleteSql();
			
			boolean  res = bSqlServ.dbWrite(cn, sql, fields2);
			if(!res){
				common.setAppFailMsg(msg , " 删除属性记录失败，请检查！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return;
			}
			
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);
			return;
			
		} catch(Exception ex) 
		{
			logger.error("System error , param get about info" + ex);
			common.setAppFailMsg(msg , ex.getMessage());
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}

	/**
	 * 更新一条指定记录
	 * @see net.IUserbasicService.module.other.IUserService#update(net.swift.common.ReturnMessage)
	 */
	public void update(ChecklogForm theForm, ReturnMessage msg) 
	{
		
		Connection cn = null;
		try
		{
			cn = DbcpPool.getConnection();
			DbcpPool.beginTran(cn);

			ChecklogServiceSqlMysql servSql = new ChecklogServiceSqlMysql();
			BasicSqlService  bSqlServ = new BasicSqlService();

			//取得FORM中传入的查询参数
			Object[] fields = new Object[9];
			
			fields[0] = theForm.getCheckerType();
			fields[1] = theForm.getCheckerID();
			fields[2] = theForm.getCheckerLevel();
			fields[3] = theForm.getUserID();
			fields[4] = theForm.getUserLevel();
			fields[5] = DatetimeUtil.getDatetime();
			fields[6] = theForm.getSeqID();
			
			String sql = servSql.getUpdateSql();
						
			boolean  res = bSqlServ.dbWrite(cn, sql, fields);
			if(!res){
				common.setAppFailMsg(msg , " 更新属性记录失败！");
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
				return ;
			}
			DbcpPool.commitTran(cn);
			DbcpPool.closeTran(cn);
			DbcpPool.returnConnection(cn);

		} catch(Exception ex) 
		{
			common.setAppFailMsg(msg , ex.getMessage());
			logger.error("System error , param get about info" + ex);
			try 
			{
				DbcpPool.rollBackTran(cn);
				DbcpPool.closeTran(cn);
				DbcpPool.returnConnection(cn);
			} catch(Exception ex1) 
			{
				ex1.printStackTrace();
				logger.error("事务回滚操作失败!ExaServiceImpl->add(01)" , ex1);
			}			
		} finally {
			DbcpPool.returnConnection(cn);
		}
		
		return ;
	}

}
